﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Security.Cryptography;

namespace mamnontuoitho7.AppCode
{


    public class Teacher : Member
    {
        public ConnectGetData _connectData;
        SqlCommand _cmd;
        String _sql = "";
        General _general;

        public Teacher() 
        {
            _connectData = new ConnectGetData();
            _cmd = new SqlCommand(_sql, _connectData.GetConnect());
            _general = new General();
        }

        /// <summary>
        /// Get teacher list of a class based on classID
        /// </summary>
        /// <param name="classID"></param>
        /// <returns></returns>
        public DataTable GetClassTeacher(String classID)
        {
            string sql = "select MemberID, FullName, Avatar from Member where ClassID = @classID  and MemberVisible = 1";
            SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
            cmd.Parameters.AddWithValue("@classID", classID);

            return _connectData.GetDataTable(cmd);
        }

        /// <summary>
        /// Get details of a teacher based memberID
        /// </summary>
        /// <param name="teacherID"></param>
        /// <returns></returns>
        public DataRow GetTeacherDetails(String teacherID)
        {
            string sql = "select * from TeacherDetailsView where memberID = @teacherID";
            SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
            cmd.Parameters.AddWithValue("@teacherID", teacherID);

            return _connectData.GetDataTable(cmd).Rows[0];
        }


        /// <summary>
        /// Update new information for teacher
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="fullname"></param>
        /// <param name="sex"></param>
        /// <param name="birthday"></param>
        /// <param name="address"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        /// <param name="avatar"></param>
        /// <param name="memberVisible"></param>
        /// <param name="classID"></param>
        /// <returns></returns>
        public int UpdateTeacher(String memberName,String memberID, String fullname, String sex, String birthday, String address, String email, String phone, String avatar, String memberVisible, String classID)
        {
            try
            {
                SqlConnection con = _connectData.GetConnect();
                string sqlUp = "update member set MemberName = @memberName, "+
                                                "fullname = @fullname, " +
                                                " sex = @sex, " +
                                                " birthday = @birthday, " +
                                                " address = @address, " +
                                                " email = @email, " +
                                                " phone = @phone, " +
                                                " avatar = @avatar, " +
                                                " classID = @classID, " +
                                                " MemberVisible = @memberVisible " +
                                                " where memberID = @memberID ";

                SqlCommand cmd = new SqlCommand(sqlUp, con);
                cmd.Parameters.AddWithValue("@memberName", memberName);
                cmd.Parameters.AddWithValue("@fullname", fullname);
                cmd.Parameters.AddWithValue("@sex", sex);
                cmd.Parameters.AddWithValue("@birthday", birthday);
                cmd.Parameters.AddWithValue("@address", address);
                cmd.Parameters.AddWithValue("@email", email);
                cmd.Parameters.AddWithValue("@phone", phone);
                cmd.Parameters.AddWithValue("@avatar", avatar);
                cmd.Parameters.AddWithValue("@classID", classID);
                cmd.Parameters.AddWithValue("@memberVisible", memberVisible);
                cmd.Parameters.AddWithValue("@memberID", memberID);

                con.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            catch (SqlException sqlEx)
            {
                System.Diagnostics.Debug.WriteLine(sqlEx.Message);
                return sqlEx.Number;
            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return 0;
            }

            return 1;
        }


        /// <summary>
        /// Update information of teacher from Teacher page
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="sex"></param>
        /// <param name="birthday"></param>
        /// <param name="address"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        /// <returns></returns>
        internal bool UpdateTeacher(String memberID, String sex, String birthday, String address, String email, String phone, String pass)
        {
            try
            {
                SqlConnection con = _connectData.GetConnect();
                string sqlUp = "";
                if (pass == "")
                {
                    sqlUp = "update member set" +   " sex = @sex, " +
                                                    " birthday = @birthday, " +
                                                    " address = @address, " +
                                                    " email = @email, " +
                                                    " phone = @phone " +
                                                    " where memberID = @memberID ";
                }
                else
                {
                    sqlUp = "update member set" +   " sex = @sex, " +
                                                    " birthday = @birthday, " +
                                                    " address = @address, " +
                                                    " email = @email, " +
                                                    " pass = @pass, " +
                                                    " phone = @phone " +
                                                    " where memberID = @memberID ";
                }
                SqlCommand cmd = new SqlCommand(sqlUp, con);
                cmd.Parameters.AddWithValue("@sex", sex);
                cmd.Parameters.AddWithValue("@birthday", birthday);
                cmd.Parameters.AddWithValue("@address", address);
                cmd.Parameters.AddWithValue("@email", email);
                cmd.Parameters.AddWithValue("@phone", phone);
                cmd.Parameters.AddWithValue("@memberID", memberID);
                if (pass != "")
                {
                    string hPassword = _general.ComputeHash(pass, new SHA256CryptoServiceProvider());
                    cmd.Parameters.AddWithValue("@pass", hPassword);
                }

                con.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return false;
            }

            return true;
        }

        /// <summary>
        /// Get Teacher list fill by Member Visible
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public DataTable GetTeacherList(String memberVisible)
        {
            if (memberVisible == "-1")
            {
                string sql = "select MemberID, FullName, Avatar, MemberVisible, ClassName, Birthday from TeacherDetailsView order by MemberID DESC";
                SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
                cmd.Parameters.AddWithValue("@memberVisible", memberVisible);
                return _connectData.GetDataTable(cmd);
            }
            else
            {
                string sql = "select MemberID, FullName, Avatar, MemberVisible, ClassName, Birthday from TeacherDetailsView where MemberVisible = @memberVisible order by MemberID DESC";
                SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
                cmd.Parameters.AddWithValue("@memberVisible", memberVisible);
                return _connectData.GetDataTable(cmd);
            }
        }





        /// <summary>
        /// Insert information for new teacher
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="memberName"></param>
        /// <param name="fullname"></param>
        /// <param name="sex"></param>
        /// <param name="birthday"></param>
        /// <param name="address"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        /// <param name="avatar"></param>
        /// <param name="memberVisible"></param>
        /// <param name="classID"></param>
        /// <returns></returns>
        internal int CreateTeacher(String memberName, String fullname, String sex, String birthday, String address, String email, String phone, String classID)
        {

            //Check Username is existed or not
            SqlConnection con = _connectData.GetConnect();
            SqlCommand cmdCheckUsername = new SqlCommand("select COUNT(MemberID) as CountRecord from Member where MemberName = @memberName ", con);
            cmdCheckUsername.Parameters.AddWithValue("@memberName", memberName);
            int checkUsername = Convert.ToInt16(_connectData.GetDataTable(cmdCheckUsername).Rows[0]["CountRecord"].ToString());
            if (checkUsername > 0)
            {
                return -99;
            }

            int memMaxID = -1;
            string hPassword = _general.ComputeHash(ConstantClass._passwordDefault, new SHA256CryptoServiceProvider());

            SqlCommand cmd = new SqlCommand("sp_TeacherInsert", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@classID", SqlDbType.NVarChar).Value = classID;
            cmd.Parameters.Add("@roleID", SqlDbType.NVarChar).Value = 3;
            cmd.Parameters.Add("@memberName", SqlDbType.NVarChar).Value = memberName;
            cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = hPassword;
            cmd.Parameters.Add("@fullname", SqlDbType.NVarChar).Value = fullname;
            cmd.Parameters.Add("@sex", SqlDbType.NVarChar).Value = sex;
            cmd.Parameters.Add("@birthday", SqlDbType.NVarChar).Value = birthday;
            cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = address;
            cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
            cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value = phone;

            SqlParameter parameterReturnValue = new SqlParameter("memMaxID", SqlDbType.Int, 4);
            parameterReturnValue.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(parameterReturnValue);

            con.Open();

            cmd.ExecuteNonQuery();
            memMaxID = Int16.Parse(cmd.Parameters["memMaxID"].Value.ToString());
            cmd.Dispose();
            con.Close();

            return memMaxID;
        }


        /// <summary>
        /// Update avatar
        /// </summary>
        /// <param name="maxMemID"></param>
        /// <param name="teacherAvatar"></param>
        internal void UpdateAvatar(int maxMemID, string teacherAvatar)
        {
            SqlConnection con = _connectData.GetConnect();
            SqlCommand cmd = new SqlCommand("update Member set avatar = @avatar where MemberID = @memberID", con);
            cmd.Parameters.AddWithValue("@avatar", teacherAvatar);
            cmd.Parameters.AddWithValue("@memberID", maxMemID);
            con.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
        }


        /// <summary>
        /// Get all available teacher of school
        /// </summary>
        /// <param name="_classID"></param>
        /// <returns></returns>
        internal DataTable GetAvailableTeacherList(String classID)
        {
            SqlConnection con = _connectData.GetConnect();
            string sqlUp = "select * from TeacherDetailsView where MemberVisible = 1 and (ClassID <> @classID or ClassID is null)";

            SqlCommand cmd = new SqlCommand(sqlUp, con);
            cmd.Parameters.AddWithValue("@classID", classID);

            return _connectData.GetDataTable(cmd);
        }

        /// <summary>
        /// Update new class for the Teachers
        /// </summary>
        /// <param name="_classID"></param>
        /// <param name="chosenKidList"></param>
        internal void KidChangeClass(string classID, string chosenKidList)
        {
            SqlConnection con = _connectData.GetConnect();
            string sqlUp = "update Member set " +
                           " ClassID = @classID" +
                           " where MemberID in (" + chosenKidList + ")";

            SqlCommand cmd = new SqlCommand(sqlUp, con);
            cmd.Parameters.AddWithValue("@classID", classID);

            con.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
        }

    }
}